CREATE VIEW [dbo].[v_stdtClsAttStat]
AS
SELECT 
 a.CK_SchoolTabName, a.CK_StudentInfoId, a.stdtName, a.CK_ClassTabID AS clsOrCsId
 , a.CK_className, a.CK_course,  a.CK_TeacherName, a.STATE
, a.fpNO, a.cardNO, a.mobile, a.tel, a.motherMobile, a.motherTel, a.chargeType, a.isPayIn, a.nextPayinDate
, COALESCE(b.totalTuition,0) AS totalTuition
, COALESCE(b.totalTermQtty,0) AS totalTermQtty
, COALESCE(e.lessonCount,0)+ COALESCE(f.lessonCount,0) AS usedLessonCount
, COALESCE(e.hourQtty,0)+ COALESCE(f.hourQtty,0) AS usedTermQtty
FROM v_studentDetail_dq AS a 
LEFT JOIN (select CK_StudentInfoid,CK_ClassTabid,sum(tuition) AS totalTuition,sum(termQtty) AS totalTermQtty from v_payinDetail1_dq group by CK_StudentInfoid,CK_ClassTabid) AS b
ON (a.CK_ClassTabID = b.CK_ClassTabid) AND (a.CK_StudentInfoid = b.CK_StudentInfoid) 
LEFT JOIN (select CK_StudentInfoid,CK_ClassTabid,count(1) as lessonCount,sum(CK_DftHourQtty) as hourQtty from v_clsAttDet1_dq where isDeductCT>0 group by CK_StudentInfoid,CK_ClassTabid) AS e 
ON (a.CK_ClassTabID = e.CK_ClassTabID) AND (a.CK_StudentInfoid = e.CK_StudentInfoid) 
LEFT JOIN (select CK_StudentInfoid,CK_ClassTabID,count(1) as lessonCount,sum(CK_DftHourQtty) as hourQtty from v_clsAttDet2_dq where oIsDeductCT>0 group by CK_StudentInfoid,CK_ClassTabID) AS f 
ON (a.CK_ClassTabID = f.CK_ClassTabID) AND (a.CK_StudentInfoid = f.CK_StudentInfoid);